﻿using System;
using System.Collections.Generic;
using System.Text;

namespace PDMCompare
{
    public class GSql
    {
        public const string T2000 = @"SELECT 
                                              d.name AS TableName,
                                              a.name AS FieldName, 
                                              CASE WHEN EXISTS
                                                  (SELECT 1
                                                 FROM dbo.sysindexes si INNER JOIN
                                                       dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN
                                                       dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN
                                                       dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK'
                                                 WHERE sc.id = a.id AND sc.colid = a.colid) THEN '1' ELSE '0' END AS IsKey,
                                              b.name AS FieldType, 
	                                          a.length AS Length, 
                                              COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS FieldPrecision, 
                                              ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS Scale,
                                              CASE WHEN a.isnullable = 1 THEN '1' ELSE '0' END AS CanNull, 
                                              ISNULL(e.text, '') AS DefaultValue
                                        FROM dbo.syscolumns a LEFT OUTER JOIN
                                              dbo.systypes b ON a.xtype = b.xusertype INNER JOIN
                                              dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND
                                              d.status >= 0 LEFT OUTER JOIN
                                              dbo.syscomments e ON a.cdefault = e.id LEFT OUTER JOIN
                                              dbo.sysproperties g ON a.id = g.id AND a.colid = g.smallid AND
                                              g.name = 'MS_Description' LEFT OUTER JOIN
                                              dbo.sysproperties f ON d.id = f.id AND f.smallid = 0 AND
                                              f.name = 'MS_Description'
                                        ORDER BY d.name";

        public const string T2005 = @"SELECT
	                                        TableName=O.name,
	                                        FieldName=C.name,
	                                        IsKey=ISNULL(IDX.PrimaryKey,N'0'),
	                                        FieldType=T.name,
	                                        Length=C.max_length,
	                                        FieldPrecision=C.precision,
	                                        Scale=C.scale,
	                                        CanNull=CASE WHEN C.is_nullable=1 THEN N'1'ELSE N'0' END,
	                                        DefaultValue=ISNULL(D.definition,N'')
                                        FROM sys.columns C 
                                            INNER JOIN sys.objects O 
                                            ON C.[object_id]=O.[object_id] 
                                            AND O.type='U' AND O.is_ms_shipped=0 INNER JOIN sys.types T
                                            ON C.user_type_id=T.user_type_id LEFT JOIN sys.default_constraints D
	                                        ON C.[object_id]=D.parent_object_id
	                                        AND C.column_id=D.parent_column_id
	                                        AND C.default_object_id=D.[object_id]
	                                        LEFT JOIN sys.extended_properties PFD
	                                        ON PFD.class=1
	                                        AND C.[object_id]=PFD.major_id
	                                        AND C.column_id=PFD.minor_id
	                                        LEFT JOIN sys.extended_properties PTB
	                                        ON PTB.class=1
	                                        AND PTB.minor_id=0
	                                        AND C.[object_id]=PTB.major_id
	                                        LEFT JOIN 
	                                        (
		                                        SELECT IDXC.[object_id], IDXC.column_id,
			                                           Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending') 
			                                               WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,
			                                           PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'1'ELSE N'0' END,
			                                           IndexName=IDX.Name
		                                        FROM sys.indexes IDX
			                                           INNER JOIN sys.index_columns IDXC
		                                        ON IDX.[object_id]=IDXC.[object_id]
			                                        AND IDX.index_id=IDXC.index_id
			                                        LEFT JOIN sys.key_constraints KC
		                                        ON IDX.[object_id]=KC.[parent_object_id]
			                                        AND IDX.index_id=KC.unique_index_id
			                                        INNER JOIN 
			                                        (
				                                        SELECT [object_id], Column_id, index_id=MIN(index_id)
				                                        FROM sys.index_columns
				                                        GROUP BY [object_id], Column_id
			                                        ) IDXCUQ
		                                        ON IDXC.[object_id]=IDXCUQ.[object_id]
			                                        AND IDXC.Column_id=IDXCUQ.Column_id
			                                        AND IDXC.index_id=IDXCUQ.index_id
	                                        ) IDX
	                                        ON C.[object_id]=IDX.[object_id]
		                                        AND C.column_id=IDX.column_id
	                                        ORDER BY O.name";

    }
}
